/**
 * @description: 
 * @version: 0.0.1
 * @author: ljr
 * @Date: 2023-09-13 14:07:35
 */

import { Provide } from '@midwayjs/core';
import { WorkingRecord } from './working_records.entity';
import { InjectRepository, InjectDataSource } from '@midwayjs/sequelize';
import { Repository, Sequelize } from 'sequelize-typescript';

@Provide()
export class WorkingRecordsModel {
  @InjectRepository(WorkingRecord)
  photoRepository: Repository<WorkingRecord>;

  @InjectDataSource()
  executeRawSQL: Sequelize;

  /**
   * 新增订单信息
   * @param {*} userNick 用户名称 
   * @param {*} unionId 钉钉的unionId 
   * @param {*} workTitle 工时填报单名称 
   * @param {*} workDay 工时归属日期
   * @param {*} workAmount 工时数量
   * @param {*} projectsName 项目名称
   * @param {*} projectsId 项目id
   * @param {*} remarks 备注
   * @param {*} hoursAmount 每小时工时金额
   * @returns 
   */
  async insertWorking(params: any): Promise<any> {
    const sql = `INSERT INTO working_records 
    SET user_nick = '${params.userNick}',
      union_id = '${params.unionId}',
      work_title = '${params.workTitle}',
      start_time = '${params.startTime}',
      end_time = '${params.endTime}',
      hours_amount = ${params.hoursAmount},
      work_amount = ${params.workAmount},
      projects_name = '${params.projectsName}',
      projects_id = ${params.projectsId},
      remarks = '${params.remarks}',
      created_at = now(),
      updated_at = now();`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }
  /**
   * 分页查询获取总条数
   * @param {*} unionId   用户union_id
   * @returns 
   */
  async getTotalNum(unionId: string): Promise<any> {
    const sql = `SELECT count(1) as num from working_records where union_id = '${unionId}';`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }
  /**
   * 分页查询获取列表
   * @param {*} pageNum 
   * @param {*} pageSize
   * @returns 
   */
  async getDataByPage(pageNum: number, pageSize: number, unionId: string): Promise<any> {
    const _limit = (pageNum - 1) * pageSize;
    const sql = `SELECT id,user_nick,union_id,work_title,work_day,work_amount,
    projects_name,projects_id,remarks,created_at,updated_at,start_time,end_time,hours_amount
FROM
    working_records where union_id = '${unionId}'
ORDER BY start_time DESC LIMIT ${_limit},${pageSize};`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
   * 根据id删除订单数据
   * @param {*} id 
   * @returns 
   */
  async delWorkingById(id: number): Promise<any> {
    const sql = `delete from working_records where id = ${id}`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
  * 根据id修改工时数据
  * @param {*} id 
  * @param {*} workTitle 
  * @param {*} startTime 
  * @param {*} endTime 
  * @param {*} workAmount 
  * @param {*} projectsName 
  * @param {*} projectsId 
  * @param {*} remarks 
  * @returns 
  */
  async updateWorkingById(params: any): Promise<any> {
    const sql = `UPDATE working_records 
    SET work_title = '${params.workTitle}',
        start_time = '${params.startTime}',
        end_time = '${params.endTime}',
        work_amount = '${params.workAmount}',
        projects_name = '${params.projectsName}',
        projects_id = '${params.projectsId}',
        remarks = '${params.remarks}',
        updated_at = now()
    WHERE
        id = ${params.id}`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }
  /**
   * 工时汇总
   * 分页查询获取总条数
   * @returns 
   */
  async getAdminTotalNum(searchKey: any): Promise<any> {
    let sql = `SELECT count(1) as num from working_records;`;
    if (searchKey) {
      sql = `SELECT count(1) AS num FROM working_records AS o 
    WHERE ( o.user_nick LIKE '%${searchKey}%' 
        OR o.work_title LIKE '%${searchKey}%' 
        OR o.projects_name LIKE '%${searchKey}%');`;
    }
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
   * 分页查询获取列表
   * @param {*} pageNum 
   * @param {*} pageSize
   * @param {*} searchKey
   * @returns 
   */
  async getAdminDataByPage(pageNum: number, pageSize: number, searchKey: any): Promise<any> {
    const _limit = (pageNum - 1) * pageSize;
    let sql = `SELECT id,user_nick,union_id,work_title,work_day,work_amount,
        projects_name,projects_id,remarks,created_at,updated_at,start_time,end_time,hours_amount
    FROM
        working_records
    ORDER BY user_nick,projects_id DESC LIMIT ${_limit},${pageSize};`;
    if (searchKey) {
      sql = `SELECT id,user_nick,union_id,work_title,work_day,work_amount,
        projects_name,projects_id,remarks,created_at,updated_at,start_time,end_time,hours_amount
    FROM
        working_records WHERE ( user_nick LIKE '%${searchKey}%' 
        OR work_title LIKE '%${searchKey}%' 
        OR projects_name LIKE '%${searchKey}%')
    ORDER BY user_nick,projects_id DESC LIMIT ${_limit},${pageSize};`;
    }
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
   * 工时数据导出查询
   * @param {*} searchKey 
   * @returns 
   */
  async downloadAdminData(searchKey: any, startTime: any, endTime: any): Promise<any> {
    let sql = `SELECT id,user_nick,union_id,work_title,work_day,work_amount,
          projects_name,projects_id,remarks,created_at,updated_at,start_time,end_time,hours_amount
      FROM
          working_records
      ORDER BY created_at DESC;`;
    if (searchKey) {
      sql = `SELECT id,user_nick,union_id,work_title,work_day,work_amount,
          projects_name,projects_id,remarks,created_at,updated_at,start_time,end_time,hours_amount
      FROM
          working_records WHERE ( user_nick LIKE '%${searchKey}%' 
          OR work_title LIKE '%${searchKey}%' 
          OR projects_name LIKE '%${searchKey}%')
      ORDER BY created_at DESC;`;
    }
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
   * 工时数据导出查询  根据部门查询
   * @param {*} searchKey   搜索关键词
   * @param {*} userIds     用户id列表
   * @param {*} startTime   工时填报开始时间
   * @param {*} endTime     工时填报结束时间
   * @returns 
   */
  async downloadAdminDataByDeptId(searchKey: any, userIds: any, startTime:any, endTime:any): Promise<any> {
    let sql = `SELECT w.id,w.user_nick,w.union_id,w.work_title,w.work_day,w.work_amount,w.projects_name,w.projects_id,w.remarks,w.created_at,w.updated_at,w.start_time,w.end_time,w.hours_amount
    FROM working_records w LEFT JOIN user_records AS u ON u.union_id = w.union_id
    WHERE u.user_id IN (${userIds}) ORDER BY w.user_nick DESC;`;

    // let orderBy = " ORDER BY w.user_nick DESC";
    if (searchKey) {
      if(startTime && endTime){
        sql = `SELECT w.id,w.user_nick,w.union_id,w.work_title,w.work_day,w.work_amount,w.projects_name,w.projects_id,w.remarks,w.created_at,w.updated_at,w.start_time,w.end_time,w.hours_amount
    FROM
        working_records w LEFT JOIN user_records AS u ON u.union_id = w.union_id
    WHERE u.user_id IN (${userIds}) and w.start_time >= '${startTime}' and w.end_time <= '${endTime}' and ( w.user_nick LIKE '%${searchKey}%' 
        OR w.work_title LIKE '%${searchKey}%' 
        OR w.projects_name LIKE '%${searchKey}%')
    ORDER BY w.user_nick DESC;`;
      }else{
        sql = `SELECT w.id,w.user_nick,w.union_id,w.work_title,w.work_day,w.work_amount,w.projects_name,w.projects_id,w.remarks,w.created_at,w.updated_at,w.start_time,w.end_time,w.hours_amount
    FROM
        working_records w LEFT JOIN user_records AS u ON u.union_id = w.union_id
    WHERE u.user_id IN (${userIds}) and ( w.user_nick LIKE '%${searchKey}%' 
        OR w.work_title LIKE '%${searchKey}%' 
        OR w.projects_name LIKE '%${searchKey}%')
    ORDER BY w.user_nick DESC;`;
      }
    }else if(startTime && endTime){
      sql = `SELECT w.id,w.user_nick,w.union_id,w.work_title,w.work_day,w.work_amount,w.projects_name,w.projects_id,w.remarks,w.created_at,w.updated_at,w.start_time,w.end_time,w.hours_amount
    FROM
        working_records w LEFT JOIN user_records AS u ON u.union_id = w.union_id
    WHERE u.user_id IN (${userIds}) and w.start_time >= '${startTime}' and w.end_time <= '${endTime}' ORDER BY w.user_nick DESC;`;
    }
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
   * 工时汇总 根据部门查询
   * 分页查询获取总条数
   * @param {*} searchKey 
   * @param {*} userIds 
   * @returns 
   */
  async getAdminTotalNumByDeptId(searchKey: any, userIds: any): Promise<any> {
    let sql = `SELECT count( 1 ) AS num FROM working_records w LEFT JOIN user_records AS u ON u.union_id = w.union_id 
    WHERE u.user_id IN (${userIds});`;
    if (searchKey) {
      sql = `SELECT count(1) AS num FROM working_records AS w LEFT JOIN user_records AS u ON u.union_id = w.union_id
        WHERE u.user_id IN (${userIds}) and ( w.user_nick LIKE '%${searchKey}%' 
            OR w.work_title LIKE '%${searchKey}%' 
            OR w.projects_name LIKE '%${searchKey}%');`;
    }
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
   * 分页查询获取列表
   * @param {*} pageNum 
   * @param {*} pageSize
   * @param {*} searchKey
   * @param {*} userIds
   * @returns 
   */
  async getAdminDataByPageByDeptId(pageNum: number, pageSize: number, searchKey: any, userIds: any): Promise<any> {
    const _limit = (pageNum - 1) * pageSize;
    let sql = `SELECT w.id,w.user_nick,w.union_id,w.work_title,w.work_day,w.work_amount,w.projects_name,w.projects_id,w.remarks,w.created_at,w.updated_at,w.start_time,w.end_time,w.hours_amount
      FROM working_records w LEFT JOIN user_records AS u ON u.union_id = w.union_id
      WHERE u.user_id IN (${userIds}) ORDER BY w.user_nick DESC LIMIT ${_limit},${pageSize};`;
      if (searchKey) {
        sql = `SELECT w.id,w.user_nick,w.union_id,w.work_title,w.work_day,w.work_amount,w.projects_name,w.projects_id,w.remarks,w.created_at,w.updated_at,w.start_time,w.end_time,w.hours_amount
      FROM
          working_records w LEFT JOIN user_records AS u ON u.union_id = w.union_id
      WHERE u.user_id IN (${userIds}) and ( w.user_nick LIKE '%${searchKey}%' 
          OR w.work_title LIKE '%${searchKey}%' 
          OR w.projects_name LIKE '%${searchKey}%')
      ORDER BY w.user_nick DESC LIMIT ${_limit},${pageSize};`;
    }
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
   * 部门工时分组计算
   * @param {*} userIds
   * @returns 
   */
  async getAllDataByDeptId(userIds: any): Promise<any> {
    const sql = `SELECT
        u.id,
        u.nick,
        u.union_id,
        SUM(w.work_amount) as total_amount,
        GROUP_CONCAT( distinct w.projects_name) proj_name
    FROM
        user_records u
        LEFT JOIN working_records w ON u.union_id = w.union_id 
    WHERE
        u.user_id IN (${userIds}) GROUP BY u.union_id ORDER BY u.nick DESC;`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }
  /**
   * 查询当前用户所有工时信息
   * @param {*} unionId
   * @returns 
   */
  async getWorkingDataByUserId(unionId: string): Promise<any> {
    const sql = `SELECT
        id,
        user_nick,
        work_title,
        work_day,
        work_amount,
        projects_name,
        projects_id,
        remarks,
        created_at,
        updated_at,
        start_time,
        end_time,hours_amount
    FROM
        working_records
    WHERE
        union_id = '${unionId}' ORDER BY start_time DESC;`;

    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
   * 项目名称修改，更新工时绑定的项目名称
   * @param {*} projects_id 
   * @param {*} projects_name
   * @returns 
   */
  async updateProjectsNameById(projectId: number, projectName: string): Promise<any> {
    const sql = `UPDATE working_records SET projects_name = '${projectName}' WHERE projects_id = ${projectId}`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }
  /**
  * 根据项目id查询工时数据
  * @param {*} id
  * @returns 
  */
  async getWorkingByProjId(id: number): Promise<any> {
    const sql = `select id,user_nick,union_id,work_title,work_day,work_amount,projects_name,projects_id,remarks,created_at,updated_at,start_time,end_time,hours_amount from working_records
    WHERE projects_id = ${id} order by work_day desc`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
   * 更新工时金额
   * @param {*} id 
   * @param {*} hours_amount
   * @returns 
   */
  async updateHoursAmountById(id: number, hours_amount: number): Promise<any> {
    const sql = `UPDATE working_records SET hours_amount = ${hours_amount} WHERE id = ${id}`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
   * 工时汇总图表
   * 用户工时查询
   * @param startTime 
   * @param endTime 
   * @returns 
   */
  async getDataByUser(startTime: string, endTime: string): Promise<any> {
    const sql = `SELECT
        w.user_nick,
        w.union_id,
        SUM( w.work_amount ) AS total_amount 
      FROM
        working_records w WHERE w.start_time >= '${startTime}' AND w.end_time <= '${endTime}'
      GROUP BY
        w.union_id`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
   * 工时汇总图表
   * 部门用户工时查询
   * @param startTime 
   * @param endTime 
   * @param {*} userIds
   * @returns 
   */
  async getDataByDeptIdAndUser(startTime: string, endTime: string,userIds: any): Promise<any> {
    const sql = `SELECT
      w.user_nick,
      w.union_id,
      SUM( w.work_amount ) AS total_amount
    FROM
      user_records u
      LEFT JOIN working_records w ON u.union_id = w.union_id 
    WHERE
      u.user_id IN (${userIds}) AND w.start_time >= '${startTime}' AND w.end_time <= '${endTime}'
    GROUP BY u.union_id;`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

  /**
   * 工时汇总图表
   * 项目工时查询
   * @param startTime 
   * @param endTime 
   * @returns 
   */
  async getDataByProj(startTime: string, endTime: string): Promise<any> {
    const sql = `SELECT
        w.projects_name,
        w.projects_id,
        SUM( w.work_amount ) AS total_amount 
      FROM
        working_records w WHERE w.start_time >= '${startTime}' AND w.end_time <= '${endTime}'
      GROUP BY
        w.projects_id`;
    const [results] = await this.executeRawSQL.query(sql);
    return results
  }

    /**
   * 工时汇总图表
   * 部门项目工时查询
   * @param startTime 
   * @param endTime 
   * @param {*} userIds
   * @returns 
   */
    async getDataByDeptIdAndProj(startTime: string, endTime: string,userIds: any): Promise<any> {
      const sql = `SELECT
        w.projects_name,
        w.projects_id,
        SUM( w.work_amount ) AS total_amount
      FROM
        user_records u
        LEFT JOIN working_records w ON u.union_id = w.union_id 
      WHERE
        u.user_id IN (${userIds}) AND w.start_time >= '${startTime}' AND w.end_time <= '${endTime}'
      GROUP BY w.projects_id;`;
      const [results] = await this.executeRawSQL.query(sql);
      return results
    }
}